<?php

namespace App\Http\Controllers\Admin\ExportReportSample;

use App\Http\Controllers\Controller;
use DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
class ZiBoGuoWangController extends Controller
{
    private static $info = [];
    /*导出单个样品检测报告*/
    public function export(){
        $temp_FM = './storage/ExcelTemplate/单样品封面.xls'; //考虑到兼容Linux，斜杠用“/”
        if (!file_exists($temp_FM)) {
            return '单样品封面模板文件不存在';
        }
        //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
        $setting = DB::table('setting')->find(1); //
        $report_num_pre = $setting->report_num_pre; //报告编号前缀
        $company_name = $setting->name_rep; //获取检测公司名称
        
        $id = request('id');
        self::$info = $info = DB::table('a_sample_info as a')
                ->leftJoin('a_contract_gaiyao as b','a.contract_gaiyao_id','=','b.id')
                ->leftJoin('a_contract as c','a.contract_id','=','c.id')
                ->leftJoin('a_status_time as d','d.contract_id','=','c.id')
                ->where('a.id',$id)
                ->select('a.id','c.entrusted_company','b.tool_name','b.voltage','c.test_kind','a.manufacturer','a.self_num','a.guige_xinghao','a.test_time','b.std_num','a.res','a.remark','a.tester_names','a.audit_man','c.audit_man as pi_man','d.sendback_end','b.std_id','b.tool_id','c.created_at as c_create','b.class','b.class_id','b.vol_id','b.item_ids','a.bar_code','a.next_time','c.batchNum')
                ->first();
        if (!$info) {
            return '样品不存在！';
        }
        // dd($info);
        $info_pos = DB::table('a_excel_pos as B')
            ->leftJoin('a_std_tool_rel as A', 'A.id', '=', 'B.std_tool_rel_id')
            ->where(['A.std_id' => $info->std_id, 'A.tool_id' => $info->tool_id, 'B.is_default' => 1])
            ->select('B.*', 'A.ctime', 'A.remark')->first();
        // dd($info_pos);
        if (!$info_pos) {
            return "{$info->tool_name} 未指定数据在模板文件中的位置";
        }
        $posname = $info_pos->name === '默认' ? '' : $info_pos->name;
        # 检测模版文件是否存在
        $temp_tool = "./storage/ExcelTemplate/{$info->std_id}/{$info->tool_name}{$posname}.xls"; //考虑到兼容Linux，斜杠用“/”
        if (!file_exists($temp_tool)) {
            return "{$info->tool_name}的模板文件不存在";
        }
        /***********************************************样品  start***************************************************/
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($temp_tool); //读取模板文件
            $spreadsheet->getProperties()->setTitle((string)$info->self_num ?: $info->bar_code);
            $worksheet = $spreadsheet->getSheet(0); //按索引获取表单，从 0 开始
            $worksheet->setTitle($info->tool_name.$info->std_id);
            // $headerContent = "&R{$company_name}     {$report_num_pre}{$info->batchNum}"; //眉头内容
            // $worksheet->getHeaderFooter()->setOddHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(样品信息页眉头)  Odd
            // $worksheet->getHeaderFooter()->setEvenHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(样品信息页眉头) Even
            PublicController::setPage($worksheet);
            PublicController::setPic($worksheet,'A1-JZ','./storage/ExcelTemplate/report_sign.png',200);
            //未指定行号
            if ($info_pos->line_num === '' || intval($info_pos->line_num) < 5) {
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet->disconnectWorksheets();
                unset($spreadsheet);
                return "{$v->tool_name} 未指定数据在模板文件中的行号";
            }

            $DY_RiQi = strtotime($info->c_create); //将表示时间和日期的字符串转化为相应的时间戳
            if ($info_pos->come_date !== '') {
                $comeDateCell = $info_pos->come_date;
                $worksheet->getCell($comeDateCell)->setValue(date("Y年m月d日", $DY_RiQi)); //到样日期
            }

            //------------------------------------------检测日期---------
            if ($info_pos->test_date !== ''){
                $testDateCell = $info_pos->test_date;
                $worksheet->getCell($testDateCell)->setValue($info->test_time); //检测日期
            }
            //-------------------------------------------------------------------
            if ($info_pos->sample_count !== '') {
                $worksheet->getCell($info_pos->sample_count)->setValue(1); //样品数量
            }
            $temp_hum_pressure_data = DB::table('a_item_data as B')
                ->select('B.temp', 'B.hum', 'B.pressure')
                ->leftJoin('a_sample_info as A', 'A.id', '=', 'B.sample_info_id')
                ->where('B.sample_info_id', $id)
                ->where('B.item_id', '!=', 61)
                ->get();
            // dd($temp_hum_pressure_data);
            $ExpPub = new \App\Http\Controllers\Admin\ExportReport\ExportReportPublicController();
            $se = $ExpPub::tempHumPressureSE($temp_hum_pressure_data);
            [$temp, $hum, $pressure] = $se;
            // dd($se);
            //------------------------------------------ 温度 start---------
            if ($info_pos->temp !== '') {
                $worksheet->getCell($info_pos->temp)->setValue($temp); //温度
            }
            //------------------------------------------ 温度 end ---------
            //------------------------------------------ 相对湿度 start---------
            if ($info_pos->hum !== '') {
                $worksheet->getCell($info_pos->hum)->setValue($hum); //相对湿度
            }
            //------------------------------------------ 相对湿度 end ---------
            //------------------------------------------ 大气压力 start---------
            if ($info_pos->pressure !== '') {
                $worksheet->getCell($info_pos->pressure)->setValue($pressure); //大气压力
            }
            //------------------------------------------ 大气压力 end ---------
            //------------------------------------------ 天气 start---------
            $wea = DB::table('a_item_data as B')->leftJoin('a_sample_info as A', 'A.id', '=', 'B.sample_info_id')->where('A.id', $id)->where('wea', '!=', '')->value('wea');
            // dd($wea);
            if ($info_pos->wea !== '' && $wea) {
                $worksheet->getCell($info_pos->wea)->setValue($wea);
            }
            //------------------------------------------ 天气 end ---------

            $rowIndex = $initRowIndex = $info_pos->line_num;
            $sampleCount = 1;
            if ($info_pos->serial_num1 !== '') {
                $worksheet->getCell($info_pos->serial_num1 . $rowIndex)->setValue(1); //序号
            }
            if ($info_pos->sample_num1 !== '') {
                $worksheet->getCell($info_pos->sample_num1 . $rowIndex)->setValue($info->bar_code.' '); //样品号
            }
            $self_num1 = $info_pos->self_num1;
            if ($self_num1 !== '') {

                //设置标题 start
                $row = $rowIndex - 1;
                $colRow = $self_num1 . $row;
                if (trim(str_replace("\n",'',$worksheet->getCell($colRow)->getValue())) == "样品自编号") {
                    $worksheet->getCell($colRow)->setValue('样品编号');
                }else{
                    $mergeRange = $worksheet->getCell($self_num1.$row)->getMergeRange();
                    if ($mergeRange) {
                        list($colRow,$end) = explode(':',$mergeRange);
                        if (trim(str_replace("\n",'',$worksheet->getCell($colRow)->getValue())) == "样品自编号") {
                            $worksheet->getCell($colRow)->setValue('样品编号');
                        }
                    }
                }
                //设置标题 end
                $worksheet->getCell($self_num1 . $rowIndex)->setValue($info->self_num); //自编号
                /*++++++++++++++++设置单元行高度 start++++++++++++++++*/
                $self_num_col_min = ABC2decimal($self_num1);
                $self_num_col_max = ABC2decimal($info_pos->self_num2);
                $fontSize = $worksheet->getStyle($self_num1 . $rowIndex)->getFont()->getSize();
                //字体是10号字的情况下，19个数字占17.875宽度  ，计算得出一个数字占大约0.940789宽度
                $width = 0;
                for($i = $self_num_col_min; $i<=$self_num_col_max; $i++){
                    $width += $worksheet->getColumnDimensionByColumn($i)->getWidth();
                }
                $lineCount = floor($width/0.940789); //计算每行占用的文字
                $lines = ceil(strlen($info->self_num)/$lineCount) ?: 1; //计算总共占用行数
                $height = $worksheet->getRowDimension($rowIndex)->getRowHeight();
                $height2 = $lines * 14;
                if ($height2 > $height) {
                    $worksheet->getRowDimension($rowIndex)->setRowHeight($height2);
                }
                /*++++++++++++++++设置单元行高度 start++++++++++++++++*/
            }
            if ($info_pos->voltage1 !== '') {
                $voltageWithoutKV = str_replace("kV", "", $info->voltage); //将额定电压中的 kV去掉
                $worksheet->getCell($info_pos->voltage1 . $rowIndex)->setValue($voltageWithoutKV); //额定电压
            }
            if ($info_pos->class1 !== '') {
                $worksheet->getCell($info_pos->class1 . $rowIndex)->setValue($info->class); //分类
            }
            $res = $info->res;
            $panding = $res == 1 ? '合格' : ($res == 2 ? '不合格' : '');
            if ($info_pos->res1 !== '') {
                $worksheet->getCell($info_pos->res1 . $rowIndex)->setValue($panding); //判定
            }
            //有效日期有位置 而且 周期不为0或null
            if ($info_pos->valid_date1 !== '') {
                if ($res == 1) {
                    //合格
                    $test_time = $info->test_time; //试验日期
                    $valid_date = $info->next_time; //下次试验日期
                }
                else {
                    # 不合格
                    $valid_date = "—";
                }
                $worksheet->getCell($info_pos->valid_date1 . $rowIndex)->setValue($valid_date); //有效日期
            }
            //------------------------------------------ 试验条件 start ---------
            $key_condition = DB::table('a_condition_key')->select('id', 'item_id', 'pos_start', 'pos_end')->where([['std_id', '=', $info->std_id], ['tool_id', '=', $info->tool_id], ['pos_start', '!=', ''], ['pos_end', '!=', '']])->get();
            foreach ($key_condition as $vvv) {
                $arr_item_id = explode(',', $info->item_ids); //字符串 --》数组
                $b = in_array($vvv->item_id, $arr_item_id); //in_array($value, $array);
                if ($b) {
                    # code...
                    $val_condition = DB::table('a_condition')->where('condition_key_id', '=', $vvv->id)->whereIn('vol_class_id', [0, $info->vol_id, $info->class_id])->value('val');
                }
                else {
                    # code...
                    $val_condition = '/';
                }
                $vvvstart = $vvv->pos_start;
                $vvvend = $vvv->pos_end;

                if ($vvvstart && $vvvend) {
                    $worksheet->mergeCells($vvvstart . $rowIndex . ':' . $vvvend . $rowIndex); //试验条件  合并单元格
                    $worksheet->getCell($vvvstart . $rowIndex)->setValue($val_condition); //试验条件
                }
            } //内内层 foreach 结尾
            // dd($key_condition);
            //------------------------------------------ 试验条件 end ---------

            //------------------------------------------ 单项目的检测结果（符合、不符合） start ---------
                $item_res = DB::table('a_std_tool_item_rel')->select('id', 'testitem_id', 'pos_start', 'pos_end')->where([['std_id', '=', $info->std_id], ['tool_id', '=', $info->tool_id]])->get();
                $resDef = '';
                foreach ($item_res as $k => $vvv) {
                    $vvvstart = $vvv->pos_start;
                    $vvvend = $vvv->pos_end;
                    if ($vvvstart && $vvvend) {
                        if ($k === 0) {
                            $resDef = $worksheet->getCell($vvvstart.$initRowIndex)->getValue();
                        }
                        $arr_item_id = explode(',', $info->item_ids); //字符串 --》数组
                        $b = in_array($vvv->testitem_id, $arr_item_id); //in_array($value, $array);
                        if ($b) {
                            $test_item = DB::table('a_item_data')->where([
                                    ['sample_info_id', '=', $info->id],
                                    ['item_id', '=', $vvv->testitem_id]
                                ])->select('remark','res')->first();
                            $test_res = $test_item->res;
                            //结论,0-不符合，1-符合，2-未录入
                            if($test_res === 0) {
                                $remark = $test_item->remark;
                                $test_res = $remark == "" || $remark == "无" ? '不符合' : $remark;
                            }
                            elseif($test_res === 1) {
                                $test_res = $resDef ?: '符合';
                            }
                            elseif ($test_res === 3) {
                                $test_res = '/';
                            }
                            else {
                                $test_res = '';
                            }
                        }
                        else {
                            $test_res = '/';
                        }
                        $worksheet->getCell($vvvstart . $rowIndex)->setValue($test_res); //单项目的检测结果
                    }
                    $key_data = DB::table('a_testdata_key')
                        ->select('id', 'item_id', 'pos_start', 'pos_end', 'std_tool_item_rel_id','line_num')
                        ->where('std_tool_item_rel_id',$vvv->id)
                        ->where([
                            ['pos_start', '!=', ''], 
                            ['pos_end', '!=', '']
                        ])
                        ->get();
                    //------------------------------------------ 试验数据 start ---------
                    foreach ($key_data as $kdKey => $vvv2) {
                        $arr_item_id = explode(',', $info->item_ids); //字符串 --》数组
                        $b = in_array($vvv2->item_id, $arr_item_id); //in_array($value, $array);
                        if ($b) {
                            # code...
                            $val_testdata = DB::table('a_input_data')->where([['sample_info_id', '=', $info->id], ['testdata_key_id', '=', $vvv2->id]])->value('val');
                            // dd( "{$val_testdata} --- {$info->id}---- {$vvv2->id}");
                        } else {
                            # code...
                            $val_testdata = '/';
                        }

                        try {
                            $vvv2start = $vvv2->pos_start;
                            $vvv2end = $vvv2->pos_end;
                            $vvv2LineNum = $vvv2->line_num;
                        }catch(\Exception $exception){
                            $vvv2start = $vvv2end = $vvv2LineNum = '';
                        }
                        if ($vvv2start && $vvv2end && $val_testdata) {
                            $rowIndexItemData = $vvv2LineNum ? $rowIndex+($vvv2LineNum-$initRowIndex) : $rowIndex;
                            $worksheet->getCell($vvv2start . $rowIndexItemData)->setValue(' '.$val_testdata.' '); //试验数据
                        }
                    }
                    //------------------------------------------ 试验数据 end --------- 
                } //内内层 foreach 结尾
            //------------------------------------------ 单项目的检测结果（符合、不符合） end   ---------
            $sign = $res == 1 ? 'report_sign_hege' : ($res == 2 ? 'report_sign_unhege' : '');
            $report_panding_sign_path = "./storage/ExcelTemplate/{$sign}.png"; //考虑到兼容Linux，斜杠用“/”
            //检测结论，检验结论
            $highestrow = $worksheet->getHighestRow();
            $lastRowIndex = $rowIndex;
            for ($i=$highestrow; $i >= $lastRowIndex ; $i--) { 
                $rowIndex = $i;
                $jlcellstr = trim($worksheet->getCell('A' . $rowIndex)->getValue());
                if ($jlcellstr == '检测结论' || $jlcellstr == '检验结论') {
                    $range = $worksheet->getCell('A' . $rowIndex)->getMergeRange();
                    $rangeAry = explode(':', $range);
                    $ABCpos = dec2ABC(ABC2decimal(substr($rangeAry[1], 0, 1)) + 1);
                    $colRow = $ABCpos . $rowIndex;
                    // $worksheet->getCell($colRow)->setValue($panding);
                    $worksheet->getRowDimension($rowIndex)->setRowHeight(40);
                    PublicController::setPic($worksheet,$colRow.'-JZ',$report_panding_sign_path,50);
                    break;
                }
            }
            $lastCol = $worksheet->getHighestColumn();
            $worksheet->getStyle("A{$initRowIndex}:{$lastCol}{$highestrow}")->getAlignment()->setWrapText(true);
        /***********************************************样品  end***************************************************/
            // dd($item_res);
            //添加封面(写在样品模板)
        /***********************************************封面1 start***************************************************/
            $spreadsheet2 = \PhpOffice\PhpSpreadsheet\IOFactory::load($temp_FM); //读取模板文件
            $worksheet = $spreadsheet2->getSheetByName('封面');

            $worksheet->setCellValue('P25',$info->entrusted_company);
            $worksheet->setCellValue('P26',$info->tool_name);
            $worksheet->setCellValue('P27',$info->guige_xinghao ?: '/');
            $worksheet->setCellValue('P28',$info->test_kind ?: '性能试验（预防性试验）');
            $worksheet->setCellValue('A44',$setting->name_rep);
            PublicController::setPage($worksheet);
            PublicController::setPic($worksheet,'R38','./storage/ExcelTemplate/report_sign.png',200);
            PublicController::setPic($worksheet,'Y33','./storage/images/managers/'.$info->audit_man.'.png',30); //审核人
            PublicController::setPic($worksheet,'Y35','./storage/images/managers/'.$info->pi_man.'.png',30); //批准人
            $spreadsheet->addExternalSheet($worksheet,0); //添加外部工作表
        /***********************************************封面1 end***************************************************/
        /***********************************************封面2 start***************************************************/
            $worksheet = $spreadsheet2->getSheetByName('封面2');
            PublicController::setPic($worksheet,'G1','./storage/ExcelTemplate/report_sign.png',200);
            $worksheet->setCellValue('E1',$info->entrusted_company);
            $worksheet->setCellValue('N1',$info->manufacturer);
            $worksheet->setCellValue('E4',$info->tool_name);
            $worksheet->setCellValue('N4',$info->guige_xinghao ?: '/');
            $worksheet->setCellValue('N7',$info->self_num);
            $worksheet->setCellValue('E10',$info->test_kind ?: '性能试验（预防性试验）');
            $worksheet->setCellValue('N10',$info->test_time);
            $worksheet->setCellValue('E13',$info->std_num);
            // $worksheet->setCellValue('E21',$panding);
            PublicController::setPic($worksheet,'J25',$report_panding_sign_path,50);
            $tester_names = $info->tester_names;
            // $worksheet->setCellValue('E32',$tester_names);
            $testers = explode('，',$tester_names);
            $col = ABC2decimal('B');
            foreach($testers as $k => $tester){
                $col += 3;
                PublicController::setPic($worksheet,dec2ABC($col).'32','./storage/images/managers/'.$tester.'.png',30);
            }
            $worksheet->setCellValue('E35',$info->remark);
            PublicController::setPage($worksheet);
            // $worksheet->setCellValue('N38',$info->audit_man);
            // $worksheet->setCellValue('E41',$info->pi_man);
            // $worksheet->setCellValue('N41',$info->sendback_end);
            $spreadsheet->addExternalSheet($worksheet,1); //添加外部工作表
        /***********************************************封面2 end***************************************************/
        //将页面定位到首页
        $spreadsheet->setActiveSheetIndex(0);
        //----------直接在浏览器下载
        $self_num = $info->self_num && $info->self_num != '/' ?:$info->bar_code;
        $file_name = "检测报告{$info->tool_name}{$self_num}.xls"; //输出的文件名称
        return PublicController::saveDown($spreadsheet,$file_name,$info);
    }
}
